﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace Ocean.Transfer.Tools.Data
{
    internal class CopyTable
    {
        public static void FromSqliteToSqlServer(
            SQLiteHelper sqliteHelper,
            SqlServerHelper sqlServerHelper,
            string sqlite_tableName,
            string sqlserver_tableName,
            int pageSize = 1000)
        {
            DataTable dt_to = sqlServerHelper.ExecuteQuery($"SELECT top 1 * FROM {sqlserver_tableName}") ?? new DataTable();

            // 清空指定表的数据
            // 将清空表数据的操作移到事务开始后
            int offset = 0;

            if (sqlServerHelper.GetType()
                .GetField("_conn", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance)
                ?.GetValue(sqlServerHelper) is not SqlConnection sqlConn)
                throw new InvalidOperationException("无法获取 SQL Server 连接对象。");

            SqlTransaction? transaction = null;
            try
            {
                if (sqlConn.State != ConnectionState.Open)
                    sqlConn.Open();
                transaction = sqlConn.BeginTransaction();

                // 清空指定表的数据（移到事务中）
                string sql = $"DELETE FROM {sqlserver_tableName}";
                using (var cmd = new SqlCommand(sql, sqlConn, transaction))
                {
                    cmd.ExecuteNonQuery();
                }


                while (true)
                {
                    // 从 SQLite 分页查询数据
                    string sqliteQuery = $"SELECT * FROM {sqlite_tableName} LIMIT {pageSize} OFFSET {offset}";
                    DataTable dt = sqliteHelper.ExecuteQuery(sqliteQuery) ?? new DataTable();

                    if (dt.Rows.Count == 0)
                        break;

                    // 构建 SQL Server 插入语句
                    foreach (DataRow row in dt.Rows)
                    {
                        var columns = string.Join(",", dt.Columns.Cast<DataColumn>()
                            .Where(c =>
                            {
                                // 排除类型为时间戳的字段
                                if (dt_to.Columns.Contains(c.ColumnName))
                                {
                                    var colType = dt_to.Columns[c.ColumnName]?.DataType;
                                    if (colType == typeof(byte[]) &&
                                        (c.ColumnName.Equals("timestamp", StringComparison.OrdinalIgnoreCase) ||
                                         c.ColumnName.Equals("rowversion", StringComparison.OrdinalIgnoreCase)))
                                    {
                                        return false;
                                    }
                                }
                                return true;
                            })
                            .Select(c => $"[{c.ColumnName}]"));
                        var values = string.Join(",", dt.Columns.Cast<DataColumn>().Select(c =>
                        {
                            var value = row[c];
                            if (value == DBNull.Value) return "NULL";
                            if (dt_to.Columns.Contains(c.ColumnName))
                            {
                                var colType = dt_to.Columns[c.ColumnName]?.DataType;
                                if (colType == typeof(DateTime))
                                {
                                    if (value is DateTime dateTimeValue)
                                    {
                                        return $"'{dateTimeValue:yyyy-MM-dd HH:mm:ss.fff}'";
                                    }
                                    else if (value is string strValue && DateTime.TryParse(strValue, out var parsedDate))
                                    {
                                        return $"'{parsedDate:yyyy-MM-dd HH:mm:ss.fff}'";
                                    }
                                    else
                                    {
                                        return "NULL";
                                    }
                                }
                                else if (colType == typeof(string))
                                {
                                    string stringValue = value?.ToString() ?? string.Empty;
                                    // 清理不可见字符和控制字符，只保留可打印字符和空白字符
                                    var cleanValue = new string(stringValue.Where(c => !char.IsControl(c) || char.IsWhiteSpace(c)).ToArray());
                                    return $"'{cleanValue.Replace("'", "''")}'";
                                }
                                else if (colType == typeof(bool))
                                {
                                    if (value is bool boolValue)
                                        return boolValue ? "1" : "0";
                                    if (value is int intValue)
                                        return (intValue != 0) ? "1" : "0";
                                    if (value is string strValue)
                                        return (strValue == "1" || strValue.Equals("true", StringComparison.CurrentCultureIgnoreCase)) ? "1" : "0";
                                    //return "0";

                                    if (value.ToString() == "1" || value.ToString() == "0")
                                    {
                                        return value.ToString();
                                    }
                                    else
                                    {
                                        return "NULL";
                                    }
                                }
                                else if (colType == typeof(int) || colType == typeof(long) || colType == typeof(short))
                                {
                                    if (value == DBNull.Value || value == null || string.IsNullOrWhiteSpace(value?.ToString()))
                                        return "NULL";
                                    return value.ToString() ?? "NULL";
                                }
                                else if (colType == typeof(decimal) || colType == typeof(float) || colType == typeof(double))
                                {
                                    if (value == DBNull.Value || value == null || string.IsNullOrWhiteSpace(value?.ToString()))
                                        return "NULL";
                                    return Convert.ToDecimal(value).ToString(System.Globalization.CultureInfo.InvariantCulture);
                                }
                                else if (colType == typeof(byte[]))
                                {
                                    if (value == DBNull.Value || value == null)
                                        return "NULL";
                                    byte[] bytes = (byte[])value;
                                    var hex = Convert.ToHexString(bytes);
                                    return $"0x{hex}";
                                }
                                if (value == DBNull.Value || value == null)
                                    return "NULL";
                                return value.ToString() ?? "NULL";
                            }
                            if (value is string)
                            {
                                string stringValue = value?.ToString() ?? string.Empty;
                                return $"N'{stringValue.Replace("'", "''")}'";
                            }
                            return value.ToString() ?? "NULL";
                        }));

                        // 验证columns和values不为空
                        if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(values))
                        {
                            throw new InvalidOperationException("无法生成有效的INSERT语句：列名或值为空");
                        }

                        string insertSql = $"INSERT INTO {sqlserver_tableName} ({columns}) VALUES ({values})";
                        using var cmd = new SqlCommand(insertSql, sqlConn, transaction);
                        cmd.ExecuteNonQuery();
                    }

                    offset += pageSize;
                }

                transaction.Commit();
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }


        /// <summary>
        /// 从 SQL Server 迁移数据到 SQLite
        /// </summary>
        /// <param name="sqlServerHelper">SQL Server 辅助类</param>
        /// <param name="sqliteHelper">SQLite 辅助类</param>
        /// <param name="sqlServer_tableName">SQL Server 表名</param>
        /// <param name="sqlite_tableName">SQLite 表名</param>
        /// <param name="pageSize">每页处理的记录数</param>
        public static void FromSqlServerToSQLite(
            SqlServerHelper sqlServerHelper,
            SQLiteHelper sqliteHelper,
            string sqlServer_tableName,
            string sqlite_tableName,
            int pageSize = 1000)
        {
            // 获取 SQL Server 表结构
            DataTable dt_from = sqlServerHelper.ExecuteQuery($"SELECT TOP 1 * FROM [{sqlServer_tableName}]") ?? new DataTable();
            
            // 获取 SQLite 表结构（如果表存在）
            DataTable dt_to = new DataTable();
            try
            {
                dt_to = sqliteHelper.ExecuteQuery($"SELECT * FROM {sqlite_tableName} LIMIT 1") ?? new DataTable();
            }
            catch
            {
                // 如果表不存在，创建表
                CreateTableFromSqlServer(sqlServerHelper, sqliteHelper, sqlServer_tableName, sqlite_tableName);
                dt_to = sqliteHelper.ExecuteQuery($"SELECT * FROM {sqlite_tableName} LIMIT 1") ?? new DataTable();
            }

            int offset = 0;

            if (sqlServerHelper.GetType()
                .GetField("_conn", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance)
                ?.GetValue(sqlServerHelper) is not SqlConnection sqlConn)
                throw new InvalidOperationException("无法获取 SQL Server 连接对象。");

            SqlTransaction? sqlTransaction = null;
            try
            {
                if (sqlConn.State != ConnectionState.Open)
                    sqlConn.Open();
                sqlTransaction = sqlConn.BeginTransaction();

                // 清空 SQLite 表的数据
                string clearSql = $"DELETE FROM {sqlite_tableName}";
                sqliteHelper.ExecuteNonQuery(clearSql);

                while (true)
                {
                    // 从 SQL Server 分页查询数据
                    string sqlServerQuery = $"SELECT * FROM [{sqlServer_tableName}] ORDER BY (SELECT NULL) OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY";
                    DataTable dt;
                    using (var cmd = new SqlCommand(sqlServerQuery, sqlConn, sqlTransaction))
                    {
                        using (var adapter = new SqlDataAdapter(cmd))
                        {
                            dt = new DataTable();
                            adapter.Fill(dt);
                        }
                    }

                    if (dt.Rows.Count == 0)
                        break;

                    // 构建 SQLite 插入语句
                    foreach (DataRow row in dt.Rows)
                    {
                        var columns = string.Join(",", dt.Columns.Cast<DataColumn>()
                            .Where(c =>
                            {
                                // 排除自增ID列（如果SQLite表中已存在）
                                if (dt_to.Columns.Contains(c.ColumnName))
                                {
                                    var colType = dt_to.Columns[c.ColumnName]?.DataType;
                                    // 可以根据需要添加更多过滤逻辑
                                }
                                return true;
                            })
                            .Select(c => $"[{c.ColumnName}]"));
                        
                        var values = string.Join(",", dt.Columns.Cast<DataColumn>().Select(c =>
                        {
                            var value = row[c];
                            if (value == DBNull.Value) return "NULL";
                            
                            if (dt_to.Columns.Contains(c.ColumnName))
                            {
                                var colType = dt_to.Columns[c.ColumnName]?.DataType;
                                if (colType == typeof(DateTime))
                                {
                                    if (value is DateTime dateTimeValue)
                                    {
                                        return $"'{dateTimeValue:yyyy-MM-dd HH:mm:ss}'";
                                    }
                                    else if (value is string strValue && DateTime.TryParse(strValue, out var parsedDate))
                                    {
                                        return $"'{parsedDate:yyyy-MM-dd HH:mm:ss}'";
                                    }
                                    else
                                    {
                                        return "NULL";
                                    }
                                }
                                else if (colType == typeof(string))
                                {
                                    string stringValue = value?.ToString() ?? string.Empty;
                                    // 清理不可见字符和控制字符，只保留可打印字符和空白字符
                                    var cleanValue = new string(stringValue.Where(c => !char.IsControl(c) || char.IsWhiteSpace(c)).ToArray());
                                    return $"'{cleanValue.Replace("'", "''")}'";
                                }
                                else if (colType == typeof(bool))
                                {
                                    if (value is bool boolValue)
                                        return boolValue ? "1" : "0";
                                    if (value is int intValue)
                                        return (intValue != 0) ? "1" : "0";
                                    if (value is string strValue)
                                        return (strValue == "1" || strValue.Equals("true", StringComparison.CurrentCultureIgnoreCase)) ? "1" : "0";
                                    return "NULL";
                                }
                                else if (colType == typeof(int) || colType == typeof(long) || colType == typeof(short))
                                {
                                    if (value == DBNull.Value || value == null || string.IsNullOrWhiteSpace(value?.ToString()))
                                        return "NULL";
                                    return value.ToString() ?? "NULL";
                                }
                                else if (colType == typeof(decimal) || colType == typeof(float) || colType == typeof(double))
                                {
                                    if (value == DBNull.Value || value == null || string.IsNullOrWhiteSpace(value?.ToString()))
                                        return "NULL";
                                    return Convert.ToDecimal(value).ToString();
                                }
                                else if (colType == typeof(byte[]))
                                {
                                    if (value == DBNull.Value || value == null)
                                        return "NULL";
                                    byte[] bytes = (byte[])value;
                                    var hex = Convert.ToHexString(bytes);
                                    return $"X'{hex}'";
                                }
                            }
                            
                            // 默认处理
                            if (value == DBNull.Value || value == null)
                                return "NULL";
                            if (value is string defaultStringValue)
                            {
                                // 清理不可见字符和控制字符，只保留可打印字符和空白字符
                                var cleanValue = new string(defaultStringValue.Where(c => !char.IsControl(c) || char.IsWhiteSpace(c)).ToArray());
                                return $"'{cleanValue.Replace("'", "''")}'";
                            }
                            if (value is DateTime dateTime)
                                return $"'{dateTime:yyyy-MM-dd HH:mm:ss}'";
                            
                            return value.ToString() ?? "NULL";
                        }));

                        // 验证columns和values不为空
                        if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(values))
                        {
                            throw new InvalidOperationException("无法生成有效的INSERT语句：列名或值为空");
                        }

                        string insertSql = $"INSERT INTO {sqlite_tableName} ({columns}) VALUES ({values})";
                        sqliteHelper.ExecuteNonQuery(insertSql);
                    }

                    offset += pageSize;
                }

                sqlTransaction.Commit();
            }
            catch
            {
                if (sqlTransaction != null)
                {
                    sqlTransaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }

        /// <summary>
        /// 根据 SQL Server 表结构创建 SQLite 表
        /// </summary>
        public static void CreateTableFromSqlServer(SqlServerHelper sqlServerHelper, SQLiteHelper sqliteHelper, string sqlServer_tableName, string sqlite_tableName)
        {
            // 获取 SQL Server 表结构
            var dt = sqlServerHelper.ExecuteQuery($"SELECT TOP 1 * FROM [{sqlServer_tableName}]") ?? new DataTable();
            
            if (dt.Columns.Count == 0)
                throw new InvalidOperationException($"无法获取表 {sqlServer_tableName} 的结构信息");

            // 构建 SQLite CREATE TABLE 语句
            var columnDefs = new List<string>();
            foreach (DataColumn column in dt.Columns)
            {
                string columnDef = $"[{column.ColumnName}] ";
                
                // 数据类型映射
                Type dataType = column.DataType;
                if (dataType == typeof(int) || dataType == typeof(short) || dataType == typeof(long))
                {
                    columnDef += "INTEGER";
                }
                else if (dataType == typeof(decimal) || dataType == typeof(float) || dataType == typeof(double))
                {
                    columnDef += "REAL";
                }
                else if (dataType == typeof(DateTime))
                {
                    columnDef += "TEXT"; // SQLite 使用 TEXT 存储日期时间
                }
                else if (dataType == typeof(bool))
                {
                    columnDef += "INTEGER"; // SQLite 使用 INTEGER 存储布尔值
                }
                else if (dataType == typeof(byte[]))
                {
                    columnDef += "BLOB";
                }
                else
                {
                    columnDef += "TEXT"; // 默认使用 TEXT
                }
                
                // 如果不允许为空，添加 NOT NULL
                if (!column.AllowDBNull && !column.AutoIncrement)
                {
                    columnDef += " NOT NULL";
                }
                
                // 如果是自增列，添加 PRIMARY KEY AUTOINCREMENT
                if (column.AutoIncrement)
                {
                    columnDef += " PRIMARY KEY AUTOINCREMENT";
                }
                
                columnDefs.Add(columnDef);
            }

            string createTableSql = $"CREATE TABLE IF NOT EXISTS {sqlite_tableName} ({string.Join(", ", columnDefs)})";
            
            sqliteHelper.ExecuteNonQuery(createTableSql);
        }

        /// <summary>
        /// 根据 SQLite 表结构创建 SQL Server 表
        /// </summary>
        public static void CreateTableFromSQLite(SQLiteHelper sqliteHelper, SqlServerHelper sqlServerHelper, string sqlite_tableName, string sqlserver_tableName)
        {
            // 获取 SQLite 表结构
            var dt = sqliteHelper.ExecuteQuery($"SELECT * FROM {sqlite_tableName} LIMIT 1") ?? new DataTable();
            
            if (dt.Columns.Count == 0)
                throw new InvalidOperationException($"无法获取表 {sqlite_tableName} 的结构信息");

            // 构建 SQL Server CREATE TABLE 语句
            var columnDefs = new List<string>();
            foreach (DataColumn column in dt.Columns)
            {
                string columnDef = $"[{column.ColumnName}] ";
                
                // 数据类型映射
                Type dataType = column.DataType;
                if (dataType == typeof(int) || dataType == typeof(short))
                {
                    columnDef += "INT";
                }
                else if (dataType == typeof(long))
                {
                    columnDef += "BIGINT";
                }
                else if (dataType == typeof(decimal) || dataType == typeof(float))
                {
                    columnDef += "DECIMAL(18, 4)";
                }
                else if (dataType == typeof(double))
                {
                    columnDef += "FLOAT";
                }
                else if (dataType == typeof(DateTime))
                {
                    columnDef += "DATETIME";
                }
                else if (dataType == typeof(bool))
                {
                    columnDef += "BIT";
                }
                else if (dataType == typeof(byte[]))
                {
                    columnDef += "VARBINARY(MAX)";
                }
                else if (dataType == typeof(string))
                {
                    // 对于字符串类型，需要确定长度
                    columnDef += "NVARCHAR(MAX)";
                }
                else
                {
                    columnDef += "NVARCHAR(MAX)"; // 默认使用 NVARCHAR(MAX)
                }
                
                // 如果不允许为空，添加 NOT NULL
                if (!column.AllowDBNull && !column.AutoIncrement)
                {
                    columnDef += " NOT NULL";
                }
                
                columnDefs.Add(columnDef);
            }

            // 检查表是否已存在，如果存在则删除
            string dropTableSql = $"IF EXISTS (SELECT * FROM sys.tables WHERE name = '{sqlserver_tableName}') DROP TABLE [{sqlserver_tableName}]";
            sqlServerHelper.ExecuteNonQuery(dropTableSql);

            string createTableSql = $"CREATE TABLE [{sqlserver_tableName}] ({string.Join(", ", columnDefs)})";
            
            sqlServerHelper.ExecuteNonQuery(createTableSql);
        }

        /// <summary>
        /// 获取 SQL Server 数据库中的所有用户表名
        /// </summary>
        /// <param name="sqlServerHelper">SQL Server 辅助类</param>
        /// <returns>所有表名列表</returns>
        public static List<string> GetAllSqlServerTables(SqlServerHelper sqlServerHelper)
        {
            var tableNames = new List<string>();
            var dt = sqlServerHelper.ExecuteQuery(
                "SELECT [name] FROM sys.tables WHERE [type] = 'U' ORDER BY [name]") ?? new DataTable();
            foreach (DataRow row in dt.Rows)
            {
                var tableName = row["name"]?.ToString();
                if (!string.IsNullOrEmpty(tableName))
                {
                    tableNames.Add(tableName);
                }
            }
            return tableNames;
        }

        /// <summary>
        /// 获取 SQL Server 数据库中的所有用户表名
        /// </summary>
        /// <param name="sqliteHelper">SQLite 辅助类</param>
        /// <returns>所有表名列表</returns>
        public static List<string> GetAllSQLiteTables(SQLiteHelper sqliteHelper)
        {
            var tableNames = new List<string>();
            var dt = sqliteHelper.ExecuteQuery(
                "SELECT [name] FROM sqlite_master WHERE type='table' order by [name]") ?? new DataTable();
            foreach (DataRow row in dt.Rows)
            {
                var tableName = row["name"]?.ToString();
                if (!string.IsNullOrEmpty(tableName))
                {
                    tableNames.Add(tableName);
                }
            }
            return tableNames;
        }
    }
}